package com.guohu.mapper;

import com.bean.*;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface GuoHuMapper {
    @Select("select * from wms_t_customer  where custID in(\n" +
            "   select customerID from wms_t_bill where wms_t_bill.billID in(\n" +
            "   select wms_t_bill_details.billID from wms_t_bill_details GROUP BY wms_t_bill_details.billID\n" +
            "   HAVING count(*) <= 10)\n" +
            "\t )")
    public List<WmsTCustomer> get();

    @Select("select * from wms_t_customer  where custID in(select custID from wms_t_bill)")
    public List<WmsTCustomer> getOutUser();

    //根据customer id获得用户拥有的物品信息
    @Select("select * from wms_t_bill_details where billID in (select billid from wms_t_bill where customerID = #{value}) and detailsIsDel = 0")
    public List<WmsTBillDetails> getInformation(String custid);

    //在通过billid查询用户id和姓名
    @Select("select customerid from wms_t_bill where billid = #{value}")
    public String getCustomerid(String billid);

    @Select("select custname from wms_t_customer where custid =  (select customerid from wms_t_bill where billid = #{value})")
    public String getCustmername(String billid);

    //获得托盘个数
    @Select("select  count(DISTINCT trayid) from wms_t_bill_details where billID in (select billid from wms_t_bill where customerID = #{value})")
    public int getTray(String custmoerId);
    //查询库存费用
    @Select("  select sum(billFeeStore) from wms_t_bill where customerID = #{value}")
    public Double getResultNum(String customerId);
    //获取其他费用
    @Select("  select sum(billFeeOther) from wms_t_bill where customerID = #{value}")
    public Double getOtherNum(String customerId);
    //插入wms_t_transfer
    @Insert("insert into wms_t_transfer(transferID,transferVender,transferBuyer,transferDate,transferEditTime) values(#{transferId},#{transferVender},#{transferBuyer},#{transferDate},#{transferEditTime})")
    public void insertRtan(WmsTTransfer wmsTTransfer);
    //根据id获取所有detailsID
    @Select("SELECT detailsID from wms_t_bill_details where billID in (SELECT billID from wms_t_bill where customerID = #{value})")
    public List<String> getdetailsID(String customerID);
    //结算时商品的件数
    @Select("select sum(currentGoodsNum) from wms_t_bill_details where billID in (select billID  from wms_t_bill where customerID = #{value})")
    public int getProNum(String customerId);
    //插入wms_t_trans_details
    @Insert("insert into  wms_t_trans_details values(#{tdetailsId},#{transferId},#{detailsId},#{tdetailsGoodsNum},#{tdetailsEditTime},#{tdetailsIsDel})")
    public void insertDetails(WmsTTransDetails wmsTTransDetails);
    //sql动态查询
    @Select("<script>select * from wms_t_transfer\n" +
            "        where 1 = 1\n" +
            "\n" +
            "        <if test=\"transferVender != ''\">\n" +
            "         and transferVender = #{transferVender}\n" +
            "        </if>\n" +
            "        <if test=\"transferBuyer != ''\">\n" +
            "            and transferBuyer = #{transferBuyer}\n" +
            "        </if>\n" +
            "        <if test=\"transferEditTime != ''\">\n" +
            "            and transferEditTime = #{transferEditTime}\n" +
            "        </if>\n" +
            "        <if test=\"transferDate != ''\">\n" +
            "            and transferDate = #{transferDate}\n" +
            "        </if></script>")
    public List<WmsTTransfer> getWmsInfor(WmsTTransfer wmsTTransfer);

    //bill表插入买家数据
    @Insert("insert into wms_t_bill (billID,customerID,billType,billDate,billFeeStore,billFeeOther,billPayable,billPaid,billEditTime,billIsDel) values(#{billId},#{customerId},#{billType},#{billDate},#{billFeeStore},#{billFeeOther},#{billPayable},#{billPaid},#{billEditTime},#{billIsDel})")
    public void insertBill(WmsTBill wmsTBill);

    //bill表插入卖家买家数据
    @Insert("insert into wms_t_bill (billID,customerID,billType,billDate,billFeeStore,billFeeOther,billPayable,billPaid,billEditTime,billIsDel) values(#{billId},#{customerId},#{billType},#{billDate},#{billFeeStore},#{billFeeOther},#{billPayable},#{billPaid},#{billEditTime},#{billIsDel})")
    public void insertBill1(WmsTBill wmsTBill);
    //查出卖家出库后的billid
    @Select("select wms_t_bill_details.billID from wms_t_bill t,wms_t_bill_details where  t.billID=wms_t_bill_details.billID and customerID=#{value}")
    public List<String> getOutUserBillid(String customerid);

    //更改出库后卖家的WmsTBill表中的billisdel为1
    @Update("update wms_t_bill set billIsDel = 1 where billID = #{value}")
    public void updateBillisDel(String billid);
    //更改出库后卖家的WmsTBillDetails表中的billisdel为1
    @Update("update wms_t_bill_details set detailsIsDel = 1 where billID = #{value}")
    public void updateDetailsDel(String billid);
    //将卖家厂库中的物品变为买家的

}
